import { Op } from 'sequelize';
import { BaseRepository } from './BaseRepository.js';
import type { DatabaseConnectionType } from '../connection.js';
import {
  Template,
} from '../models/index.js';
import type {
  TemplateAttributes,
  TemplateCreationAttributes,
} from '../models/index.js';

/**
 * Template 数据访问层
 */
export class TemplateRepository extends BaseRepository<
  Template,
  TemplateAttributes,
  TemplateCreationAttributes
> {
  constructor(connection: DatabaseConnectionType) {
    super(Template, connection);
  }

  /**
   * 根据名称查找模板
   */
  async findByName(name: string): Promise<Template | null> {
    return await this.findOne({
      where: { name },
    });
  }

  /**
   * 根据分类和框架查找模板
   */
  async findByCategoryAndFramework(
    category: string,
    framework?: string,
    options?: {
      limit?: number;
      offset?: number;
    }
  ): Promise<{ rows: Template[]; count: number }> {
    const whereClause: any = { category };
    
    if (framework) {
      whereClause.framework = framework;
    }

    return await this.findAndCountAll({
      where: whereClause,
      limit: options?.limit || 20,
      offset: options?.offset || 0,
      order: [['popularity_score', 'DESC']],
    });
  }

  /**
   * 搜索模板
   */
  async searchTemplates(
    query: string,
    options?: {
      category?: string;
      framework?: string;
      limit?: number;
      offset?: number;
    }
  ): Promise<{ rows: Template[]; count: number }> {
    const whereClause: any = {
      [Op.or]: [
        { name: { [Op.like]: `%${query}%` } },
        { description: { [Op.like]: `%${query}%` } },
      ],
    };

    if (options?.category) {
      whereClause.category = options.category;
    }

    if (options?.framework) {
      whereClause.framework = options.framework;
    }

    return await this.findAndCountAll({
      where: whereClause,
      limit: options?.limit || 20,
      offset: options?.offset || 0,
      order: [['popularity_score', 'DESC']],
    });
  }

  /**
   * 获取热门模板
   */
  async getPopularTemplates(
    limit: number = 10,
    framework?: string
  ): Promise<Template[]> {
    const whereClause: any = {};
    
    if (framework) {
      whereClause.framework = framework;
    }

    return await this.findAll({
      where: whereClause,
      order: [['popularity_score', 'DESC']],
      limit,
    });
  }

  /**
   * 更新模板受欢迎程度
   */
  async updatePopularityScore(templateId: number, increment: number = 1): Promise<void> {
    await this.model.increment('popularity_score', {
      by: increment,
      where: { id: templateId },
    });
  }

  /**
   * 获取所有分类
   */
  async getCategories(): Promise<string[]> {
    const result = await this.model.findAll({
      attributes: ['category'],
      group: ['category'],
    });

    return result.map((item: any) => item.category);
  }

  /**
   * 根据框架获取模板
   */
  async getTemplatesByFramework(
    framework: string,
    options?: {
      limit?: number;
      offset?: number;
    }
  ): Promise<{ rows: Template[]; count: number }> {
    return await this.findAndCountAll({
      where: { framework },
      limit: options?.limit || 20,
      offset: options?.offset || 0,
      order: [['popularity_score', 'DESC']],
    });
  }

  /**
   * 根据依赖查找模板
   */
  async findByDependency(dependency: string): Promise<Template[]> {
    return await this.findAll({
      where: {
        dependencies: {
          [Op.contains]: [dependency],
        },
      },
    });
  }

  /**
   * 获取模板统计信息
   */
  async getTemplateStats(): Promise<{
    total: number;
    byFramework: Array<{ framework: string; count: number }>;
    byCategory: Array<{ category: string; count: number }>;
  }> {
    const total = await this.count();

    const byFramework = await this.model.findAll({
      attributes: [
        'framework',
        [this.connection.getSequelize().fn('COUNT', '*'), 'count'],
      ],
      group: ['framework'],
      order: [[this.connection.getSequelize().literal('count'), 'DESC']],
    });

    const byCategory = await this.model.findAll({
      attributes: [
        'category',
        [this.connection.getSequelize().fn('COUNT', '*'), 'count'],
      ],
      group: ['category'],
      order: [[this.connection.getSequelize().literal('count'), 'DESC']],
    });

    return {
      total,
      byFramework: byFramework.map((item: any) => ({
        framework: item.framework,
        count: parseInt(item.dataValues.count, 10),
      })),
      byCategory: byCategory.map((item: any) => ({
        category: item.category,
        count: parseInt(item.dataValues.count, 10),
      })),
    };
  }
}